
package com.library.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
    static Connection conn = null; // 表示数据库的连接
    static PreparedStatement pstmt = null; // 表示预编译的SQL语句
    static ResultSet rest = null; // 表示查询结果集

    // 定义获取数据库连接的方法
    public static Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://192.168.30.100:3306/test_db_0310?characterEncoding=utf-8", "root", "123456");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    // 定义关闭数据库连接的方法
    public static void closeConnection() {
        try {
            if (rest != null)
                rest.close();
            if (pstmt != null)
                pstmt.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 定义执行增删改的方法
    public static <E> int jdbcByUpdate(String sql, E... params) {
        int i = 0;
        getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            for (int j = 0; j < params.length; j++) {
                pstmt.setObject(j + 1, params[j]);
            }
            i = pstmt.executeUpdate();
        } catch (SQLException exception) {
            exception.printStackTrace();
        }finally {
            closeConnection();
        }

        return i;
    }

    // 定义执行查询的方法
    public synchronized static <E> List<Map<String, Object>> jdbcBySelect(String sql, E... params) {
        List<Map<String, Object>> list = new ArrayList<>();
        getConnection();
        ResultSetMetaData rsmd = null; // 表示结果集的列数据
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            rest = pstmt.executeQuery();
            rsmd = rest.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rest.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rsmd.getColumnName(i);
                    Object value = rest.getObject(i);
                    map.put(columnName, value);
                }
                list.add(map);
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }finally{
            closeConnection();
        }
        return list;
    }
}
